import mysql.connector
from typing import List, Dict, Any

class DatabaseTool:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="20539",
            database="smart_supermarket"
        )
        self.cursor = self.connection.cursor(dictionary=True)

    def get_products_by_category(self, category: str) -> List[Dict[str, Any]]:
        query = "SELECT * FROM products WHERE category_name = %s"
        self.cursor.execute(query, (category,))
        return self.cursor.fetchall()

    def get_product_by_id(self, product_id: int) -> Dict[str, Any]:
        query = "SELECT * FROM products WHERE id = %s"
        self.cursor.execute(query, (product_id,))
        return self.cursor.fetchone()

    def get_popular_products(self, limit: int = 10) -> List[Dict[str, Any]]:
        query = """
        SELECT p.*, COALESCE(SUM(s.quantity), 0) as total_sales
        FROM products p
        LEFT JOIN sales s ON p.id = s.product_id
        GROUP BY p.id
        ORDER BY total_sales DESC
        LIMIT %s
        """
        self.cursor.execute(query, (limit,))
        return self.cursor.fetchall()

    def get_products_by_price_range(self, min_price: float, max_price: float) -> List[Dict[str, Any]]:
        query = "SELECT * FROM products WHERE price BETWEEN %s AND %s"
        self.cursor.execute(query, (min_price, max_price))
        return self.cursor.fetchall()

    def search_products(self, keyword: str) -> List[Dict[str, Any]]:
        query = """
        SELECT * FROM products 
        WHERE name LIKE %s 
        OR brand LIKE %s 
        OR product_description LIKE %s 
        OR category_name LIKE %s
        """
        search_term = f"%{keyword}%"
        self.cursor.execute(query, (search_term, search_term, search_term, search_term))
        return self.cursor.fetchall()

    def get_available_categories(self) -> List[str]:
        query = "SELECT DISTINCT category_name FROM products WHERE category_name IS NOT NULL"
        self.cursor.execute(query)
        return [row['category_name'] for row in self.cursor.fetchall()]

    def __del__(self):
        if hasattr(self, 'cursor'):
            self.cursor.close()
        if hasattr(self, 'connection'):
            self.connection.close() 